﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;

public partial class SSFGL_SSFGLALLKHList : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      if (!ProjectPrivate.GetQX("10"))
      {
        Server.Transfer("~\\blank.aspx");
      }
    }
    BindGrid();
  }

  protected void BindGrid()
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("SELECT * FROM(");
    sbSql.Append(" select a.*,b.KHJB,c.DWMC from jcxx_khxx a,jcxx_khjb b,jcxx_dw c where a.khjbid=b.khjbid and a.dwid=c.dwid ");
    if (!string.IsNullOrEmpty(txtKHBM.Text))
    {
      sbSql.Append("and a.khid='" + txtKHBM.Text + "' ");
    }
    if (!string.IsNullOrEmpty(txtKHMC.Text))
    {
      sbSql.Append("and a.khmc like '%" + txtKHMC.Text + "%' ");
    }
    if (!string.IsNullOrEmpty(txtBS.Text))
    {
      sbSql.Append("and a.kh like '%" + txtBS.Text + "%' ");
    }
    sbSql.Append(@"
)A
OUTER APPLY(
    SELECT 
        YQXZ=STUFF(
      REPLACE(
       REPLACE((select yqxz from 
   (SELECT yqxz,khid FROM jcxx_yqxz b,jcxx_khyqxz N WHERE b.yqxzid=n.yqxzid ) b
    WHERE b.khid = A.khid FOR XML AUTO), ");
    sbSql.Append("'<b yqxz=\"', ','),'\"/>', ''), 1, 1, '')) N order by a.khid desc");
    SqlParameter[] cmdParams = { new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = txtKHBM.Text;
    cmdParams[1].Value = txtKHMC.Text;
    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    ASPxGridView1.DataSource = ds;
    ASPxGridView1.DataBind();
  }

  protected void lbDelete_Command(object sender, CommandEventArgs e)
  {
    SqlParameter[] cmdParams = null;
    string sSql = "";
    List<CommandInfo> cis = new List<CommandInfo>();
    CommandInfo ci;

    sSql = "delete from SJLR_yjlr where yjid=" + e.CommandArgument.ToString();
    ci = new CommandInfo();
    ci.CommandText = sSql;
    ci.Parameters = cmdParams;
    cis.Add(ci);

    if (DbHelperSQL.ExecuteSqlTran(cis) > 0)
    {
      BindGrid();
    }
  }

  protected void btnQuery_Click(object sender, EventArgs e)
  {
    //BindGrid();
  }
}
